﻿using System.Data;
using System.Xml;
using Chaotic.DataBase.DataAccess;
using Chaotic.DataBase.DataAccess.Entity;
using MySql.Data.MySqlClient;

namespace Chaotic.DataBase.MySQLAccess
{
    public class MysqlDataAccess : AbstractDataAccess
    {
        private readonly MySqlConnection _mySqlConnection;
        private MySqlTransaction? _trans;
        private readonly string _databaseName;
 
        public MysqlDataAccess(string connectionString)
        {
            _mySqlConnection = new MySqlConnection(connectionString);
            int num = connectionString.ToLower().IndexOf("uid=", StringComparison.Ordinal) + "uid=".Length;
            int num2 = connectionString.IndexOf(';', num);
            base.Owner = connectionString.Substring(num, num2 - num).ToUpper();

            num = connectionString.ToLower().IndexOf("database=", StringComparison.Ordinal) + "database=".Length;
            num2 = connectionString.IndexOf(';', num);
            _databaseName = connectionString.Substring(num, num2 - num).ToUpper();
        }

        public override DatabaseType DatabaseType => DatabaseType.Mysql;
        public override IDbConnection DbConnection => _mySqlConnection;

        public override IDbTransaction? BeginTransaction()
        {
            _trans = _mySqlConnection.BeginTransaction();
            return _trans;
        }

        private void PrepareCommand(MySqlCommand cmd, CommandType commandType, string? commandText, QueryParameterCollection? commandParameters)
        {
            if (string.IsNullOrEmpty(commandText))
                return;

            commandText = commandText.Replace(":", "@");
            cmd.CommandType = commandType;
            cmd.CommandText = commandText;
            cmd.Connection = _mySqlConnection;
            cmd.Transaction = _trans;
            if (commandParameters is { Count: > 0 })
            {
                for (int i = 0; i < commandParameters.Count; i++)
                {
                    cmd.Parameters.AddWithValue(commandParameters[i].ParameterName, commandParameters[i].Value);
                    cmd.Parameters[i].Direction = commandParameters[i].Direction;
                }
            }
        }

        public override int ExecuteNonQuery(CommandType commandType, string? commandText, QueryParameterCollection commandParameters, DataRow row, SqlStruct? sqlStruct)
        {
            int tmpValue = 0;
            LogOutPut(delegate
            {
                MySqlCommand mysqlCommand = new MySqlCommand();
                if (sqlStruct == null)
                {
                    PrepareCommand(mysqlCommand, commandType, commandText, commandParameters);
                }
                else
                {
                    ParamField[] paramFields = sqlStruct.ParamFields;
                    foreach (ParamField paramField in paramFields)
                    {
                        if (paramField.ParamName != null && paramField.ParamName.IndexOf('_') != 1)
                        {
                            commandParameters.Add(paramField.ParamName, row[paramField.FieldName]);
                        }
                        else
                        {
                            commandParameters.Add(paramField.ParamName, row[paramField.FieldName, DataRowVersion.Original]);
                        }
                    }
                    PrepareCommand(mysqlCommand, CommandType.Text, sqlStruct.SqlString, commandParameters);
                }
                tmpValue = mysqlCommand.ExecuteNonQuery();
                mysqlCommand.Parameters.Clear();
                return tmpValue;
            }, "ExecuteNonQuery", commandText, commandParameters);
            return tmpValue;
        }

        public override DataSet? ExecuteDataset(CommandType commandType, string? commandText, QueryParameterCollection commandParameters, DataSet? ds, string tableName)
        {
            LogOutPut(delegate
            {
                MySqlCommand mysqlCommand = new MySqlCommand();
                PrepareCommand(mysqlCommand, commandType, commandText, commandParameters);
                MySqlDataAdapter mysqlDataAdapter = new MySqlDataAdapter(mysqlCommand);
                if (object.Equals(tableName, null) || tableName.Length < 1)
                {
                    mysqlDataAdapter.Fill(ds);
                }
                else
                {
                    mysqlDataAdapter.Fill(ds, tableName);
                }
                mysqlCommand.Parameters.Clear();
                return (ds != null && ds.Tables.Count > 0) ? ds.Tables[0].Rows.Count : 0;
            }, "ExecuteDataset", commandText, commandParameters);
            return ds;
        }

        public override DataSet? ExecuteDataset(string? commandText, QueryParameterCollection commandParameters, DataSet? ds, int startRecord, int maxRecords, string tableName)
        {
            LogOutPut(delegate
            {
                MySqlCommand mysqlCommand = new MySqlCommand();
                PrepareCommand(mysqlCommand, CommandType.Text, commandText, commandParameters);
                MySqlDataAdapter mysqlDataAdapter = new MySqlDataAdapter(mysqlCommand);
                if (object.Equals(tableName, null) || tableName.Trim() == string.Empty)
                {
                    mysqlDataAdapter.Fill(ds);
                }
                else
                {
                    mysqlDataAdapter.Fill(ds, startRecord, maxRecords, tableName);
                }
                mysqlCommand.Parameters.Clear();
                return (ds != null && ds.Tables.Count > 0) ? ds.Tables[0].Rows.Count : 0;
            }, "ExecuteDataset", commandText, commandParameters);
            return ds;
        }
        public override IDataReader ExecuteReader(CommandType commandType, string? commandText, QueryParameterCollection commandParameters)
        {
            IDataReader dr = null;
            LogOutPut(delegate
            {
                MySqlCommand mysqlCommand = new MySqlCommand();
                PrepareCommand(mysqlCommand, commandType, commandText, commandParameters);
                dr = mysqlCommand.ExecuteReader();
                mysqlCommand.Parameters.Clear();
                return -1;
            }, "ExecuteReader", commandText, commandParameters);
            return dr;
        }
        public override object? ExecuteScalar(CommandType commandType, string? commandText, QueryParameterCollection commandParameters)
        {
            object? tmpValue = null;
            LogOutPut(delegate
            {
                MySqlCommand mysqlCommand = new MySqlCommand();
                PrepareCommand(mysqlCommand, commandType, commandText, commandParameters);
                tmpValue = mysqlCommand.ExecuteScalar();
                mysqlCommand.Parameters.Clear();
                return -1;
            }, "ExecuteScalar", commandText, commandParameters);
            return tmpValue;
        }
        public override XmlReader ExecuteXmlReader(CommandType commandType, string? commandText, QueryParameterCollection commandParameters)
        {
            return null;
        }




        public override void FillingTables(List<TableInfo>? info)
        {
            DataTable schema = _mySqlConnection.GetSchema("Tables");
            info.AddRange(from DataRow row in schema.Rows
                          where row["TABLE_TYPE"].ToString() == "BASE TABLE"
                          select new TableInfo
                          {
                              TableName = row["TABLE_NAME"].ToString()
                          });
        }

        public override void FillingColumns(List<ColumnInfo> info)
        {
            DataTable schema = _mySqlConnection.GetSchema("Columns");
            info.AddRange(from DataRow row in schema.Rows
                          select new ColumnInfo
                          {
                              TableName = row["TABLE_NAME"].ToString(),
                              ColumnName = row["COLUMN_NAME"].ToString(),
                              ColumnIndex = Convert.ToInt32(row["ORDINAL_POSITION"].ToString()),
                              IsNull = (row["IS_NULLABLE"].ToString().ToUpper() == "YES"),
                              TypeName = row["DATA_TYPE"].ToString(),
                              Length = row["CHARACTER_MAXIMUM_LENGTH"].ToString()
                          });
        }

        public override void FillingIndexs(List<IndexInfo>? info)
        {
            DataTable schema = _mySqlConnection.GetSchema("IndexColumns");
            info.AddRange(from DataRow row in schema.Rows
                          select new IndexInfo
                          {
                              ColumnName = row["COLUMN_NAME"].ToString(),
                              TableName = row["TABLE_NAME"].ToString(),
                              IndexName = row["INDEX_NAME"].ToString(),
                              Sequence = row["SORT_ORDER"].ToString() == "A" ? "ASC" : "DESC",
                              IsUnique = false
                          });
        }

        public override void FillingRelations(List<RelationshipInfo>? info)
        {
            DataTable ForeignSchema = _mySqlConnection.GetSchema("Foreign Keys");
            EnumerableRowCollection<DataRow> dataRows = ForeignSchema.AsEnumerable();

            DataTable schema = _mySqlConnection.GetSchema("Foreign Key Columns");
            foreach(DataRow row in schema.Rows)
            {
                RelationshipInfo relationshipInfo = new RelationshipInfo
                {
                    TableName = row["TABLE_NAME"].ToString(),
                    RefKeyName = row["CONSTRAINT_NAME"].ToString(),
                    ColumnName = row["COLUMN_NAME"].ToString(),
                    RelationTableName = row["REFERENCED_TABLE_NAME"].ToString(),
                    RelationColumnName = row["REFERENCED_COLUMN_NAME"].ToString()
                };

                DataRow fsRow = dataRows.FirstOrDefault(o => o["TABLE_NAME"].ToString() == relationshipInfo.TableName);
                if(fsRow != null)
                {
                    relationshipInfo.IsCascadingUpdate = fsRow["UPDATE_RULE"].ToString() == "CASCADE";
                    relationshipInfo.IsCascadingDelete = fsRow["DELETE_RULE"].ToString() == "CASCADE";
                }

                info.Add(relationshipInfo);
            }
            info.AddRange(from DataRow row in schema.Rows
                          select new RelationshipInfo
                          {
                              TableName = row["TABLE_NAME"].ToString(),
                              RefKeyName = row["CONSTRAINT_NAME"].ToString(),
                              ColumnName = row["COLUMN_NAME"].ToString(),
                              RelationTableName = row["REFERENCED_TABLE_NAME"].ToString(),
                              RelationColumnName = row["REFERENCED_COLUMN_NAME"].ToString(),
                              
                          });
        }

        public override void FillingViews(List<ViewInfo> info)
        {
            DataTable schema = _mySqlConnection.GetSchema("Views");
            info.AddRange(from DataRow row in schema.Rows
                          where row["TABLE_SCHEMA"].ToString().ToLower() == _databaseName.ToLower()
                          select new ViewInfo
                          {
                              ViewName = row["TABLE_NAME"].ToString()
                          });
        }
        public override void FillingProcs(List<ProcInfo> info)
        {
            DataTable schema = _mySqlConnection.GetSchema("Procedures");
            info.AddRange(from DataRow row in schema.Rows
                          where row["ROUTINE_SCHEMA"].ToString().ToLower() == _databaseName.ToLower()
                          select new ProcInfo
                          {
                              ProcId = row["ROUTINE_NAME"].ToString(),
                              ProcName = row["ROUTINE_NAME"].ToString()
                          });
        }

        public override void FillingProcParams(List<ProcParamInfo> info)
        {
            DataTable schema = _mySqlConnection.GetSchema("Procedure Parameters");
            info.AddRange(from DataRow row in schema.Rows
                          where row["ROUTINE_TYPE"].ToString() == "PROCEDURE" && row["SPECIFIC_SCHEMA"].ToString().ToLower() == _databaseName.ToLower()
                          select new ProcParamInfo
                          {
                              ProcId = row["SPECIFIC_NAME"].ToString(),
                              ProcParamName = row["PARAMETER_NAME"].ToString(),
                              TypeName = row["DATA_TYPE"].ToString(),
                              Length = row["CHARACTER_MAXIMUM_LENGTH"].ToString(),
                              ParamIndex = Convert.ToInt32(row["ORDINAL_POSITION"].ToString()),
                              DataScale = row["NUMERIC_SCALE"].ToString()
                          });
        }

        public override void FillingTypeInfos(List<TypeNameInfos> info)
        {
            DataTable schema = _mySqlConnection.GetSchema("DataTypes");
            if (schema != null && schema.Rows.Count > 0)
            {
                info.AddRange(from DataRow row in schema.Rows
                              select new TypeNameInfos
                              {
                                  TypeName = row["TypeName"].ToString()
                              });
            }
        }
    }
}
